package com.sxj.corejava.code12_jdbc;

import com.sxj.corejava.util.DataAccessException;
import com.sxj.corejava.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author 石小俊
 * @date 2024年07月01日 15:38
 * 创建一张学生表,拥有以下字段:主键id、学号no唯一且不为空、姓名不为空、年龄
 * 完成以下方法
 * 1.添加学生信息
 * 2.根据学号删除指定学生信息
 * 3.根据学号修改指定学生信息
 * 4.查询所有学生信息
 * 5.根据学号查询指定学生信息
 * 6.根据姓名进行模糊查询
 * 7.根据年龄范围查询指定学生信息
 */
public class Test05_学生管理系统 {

    public static void main(String[] args) {
        showMenu();
    }


    public static void showMenu() {
        Scanner sc = new Scanner(System.in);
        System.out.println("******************欢迎使用学生管理系统******************");
        System.out.print("请选择服务(1.添加 2.删除 3.修改 4.查询所有 5.根据学号查询 6.根据姓名模糊查询 7.根据年龄范围查询):");
        int choice = sc.nextInt();
        Student student = null;
        String no = null;
        switch (choice) {
            case 1:
                student = new Student();
                System.out.print("请输入学号:");
                student.setNo(sc.next());
                System.out.print("请输入学生姓名:");
                student.setName(sc.next());
                System.out.print("请输入年龄:");
                student.setAge(sc.nextInt());
                // Student student = new Student("zj001","admin",20);
                try {
                    insertStudent(student);
                    System.out.println("提示:恭喜您,添加成功");
                } catch (DataAccessException e) {
                    e.printStackTrace();
                    System.out.println("错误:对不起,添加失败,失败原因:" + e.getMessage());
                }
                break;
            case 2:
                System.out.print("请输入需要删除的学生学号:");
                no = sc.next();
                try {
                    deleteByNo(no);
                    System.out.println("提示:恭喜您,删除成功!");
                } catch (DataAccessException e) {
                    System.out.println("错误:对不起,删除失败,失败原因:" + e.getMessage());
                }
                break;
            case 3:
                student = new Student();
                System.out.print("请输入修改的学生学号:");
                student.setNo(sc.next());
                System.out.print("请输入修改后的学生姓名:");
                student.setName(sc.next());
                System.out.print("请输入修改后的学生年龄:");
                student.setAge(sc.nextInt());
                try {
                    updateByNo(student);
                    System.out.println("提示:恭喜您,修改成功!");
                } catch (DataAccessException e) {
                    System.out.println("错误:对不起,修改失败,失败原因:" + e.getMessage());
                }
                break;
            case 4:
                try {
                    List<Student> students = selectAll();
                    System.out.println("编号\t\t学号\t\t姓名\t\t年龄");
                    for (Student stu : students) {
                        System.out.println(stu.getId() + "\t\t" + stu.getNo() + "\t\t" + stu.getName() + "\t\t" + stu.getAge());
                    }
                } catch (DataAccessException e) {
                    System.out.println("错误:对不起,查询失败,失败原因:" + e.getMessage());
                }
                break;
            case 5:
                System.out.print("请输入学号:");
                no = sc.next();
                try {
                    student = selectByNo(no);
                    System.out.println("编号:" + student.getId() + ",学号:" + student.getNo() + ",姓名:" + student.getName() + ",年龄:" + student.getAge());
                } catch (DataAccessException e) {
                    System.out.println("错误:对不起,查询失败,失败原因:" + e.getMessage());
                }
                break;
            case 6:
                System.out.print("请输入姓名:");
                String name = sc.next();
                try {
                    List<Student> students = selectByName(name);
                    System.out.println("编号\t\t学号\t\t姓名\t\t年龄");
                    for (Student stu : students) {
                        System.out.println(stu.getId() + "\t\t" + stu.getNo() + "\t\t" + stu.getName() + "\t\t" + stu.getAge());
                    }
                } catch (DataAccessException e) {
                    System.out.println("错误:对不起,查询失败,失败原因:" + e.getMessage());
                }
                break;
            case 7:
                System.out.print("请输入最小年龄:");
                int minAge = sc.nextInt();
                System.out.print("请输入最大年龄:");
                int maxAge = sc.nextInt();
                try {
                    List<Student> students = selectByAge(minAge, maxAge);
                    System.out.println("编号\t\t学号\t\t姓名\t\t年龄");
                    for (Student stu : students) {
                        System.out.println(stu.getId() + "\t\t" + stu.getNo() + "\t\t" + stu.getName() + "\t\t" + stu.getAge());
                    }
                } catch (DataAccessException e) {
                    System.out.println("错误:对不起,查询失败,失败原因:" + e.getMessage());
                }
                break;
            default:
                System.out.println("错误:您输入的选项有误,请重新输入");
                break;
        }
        showMenu();
    }

    /**
     * 添加学生信息
     * @param student
     * @throws DataAccessException
     */
    public static void insertStudent(Student student) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append("   (no,name,age)")
                    .append(" values ")
                    .append("   (?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.executeUpdate();
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据学号删除指定学生信息
     * @param no
     */
    public static void deleteByNo(String no) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" delete from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            ps.executeUpdate();
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据学号修改指定学生信息
     * @param student
     */
    public static void updateByNo(Student student) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" update t_student ")
                    .append(" set ")
                    .append("   name = ?, ")
                    .append("   age = ? ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setString(3, student.getNo());
            ps.executeUpdate();
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 查询所有学生信息
     * @return
     * @throws DataAccessException
     */
    public static List<Student> selectAll() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }

    /**
     * 根据学号查询指定学生信息
     * @param no
     * @return
     */
    public static Student selectByNo(String no) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            rs = ps.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return student;
    }

    /**
     * 根据姓名进行模糊查询
     * @param name
     * @return
     */
    public static List<Student> selectByName(String name) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student")
                    .append(" where name like ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }

    /**
     * 根据年龄范围查询指定学生信息
     * @param minAge
     * @param maxAge
     * @return
     */
    public static List<Student> selectByAge(int minAge, int maxAge) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student")
                    .append(" where age between ? and ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, minAge);
            ps.setInt(2, maxAge);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }
}
